How to use SQL Join in LINQ?

Question

Ravi Vishwakarma · Answer

Join in LINQ 
and In LINQ, join operators are used to join more than one list or the collection and get the matched data from the collection based on condition. The LINQ join operator functionality are the same as the SQL Join query.
LINQ has different types of joins 
Inner Join - it returns the elements from the collection which satisfy the condition
Left Outer Join - it return the all element from the left side collection and matched collection from right side collection
Cross Join - it returns some product of elements from the collection Group Join - 
Inner Join Example -and 
using System;
using System.Collections.Generic;
using System.Linq;
public class Program
{
and public static void Main()
and {
and  Listandlt;Subject> subjects = new Listandlt;Subject>(){
and  and new Subject() { SubjectId = 1, SubjectName = 'C#'},
and  and new Subject() { SubjectId = 2, SubjectName = 'Cand#43;and#43;'},
and  and new Subject() { SubjectId = 3, SubjectName = 'C'},
and  and new Subject() { SubjectId = 4, SubjectName = 'Python'},
and  and new Subject() { SubjectId = 5, SubjectName = 'Java'}
and  };
and  Listandlt;Student> students = new Listandlt;Student>(){
and  new Student() { StudentId = 1, Name = 'Ashu', Marks = 500,SubjectId=2 },
and  new Student() { StudentId = 2, Name = 'Shyam', Marks = 500,SubjectId=3},
and  new Student() { StudentId = 3, Name = 'Shriyam', Marks = 400,SubjectId=2 },
and  new Student() { StudentId = 4, Name = 'Sunny', Marks = 550,SubjectId=4 },
and  new Student() { StudentId = 5, Name = 'Ram', Marks = 600,SubjectId=3 },
and  new Student() { StudentId = 6, Name = 'Krishna', Marks = 550,SubjectId=2 },
and  new Student() { StudentId = 7, Name = 'Anupam', Marks = 550,SubjectId=5 }
and  } ;
and  // inner join command 
and  var result = from student in students
and  join subject in subjects
and  on student.SubjectId equals subject.SubjectId
and  select new
and  {
and  StudentName = student.Name,
and  SubjectName = subject.SubjectName,
and  ID = student.StudentId
and  };
and  Console.WriteLine('Inner Join
');
and  Console.WriteLine('{0,3} {1,-15}  {2,-10}','ID', 'StudentName', 'Subject');
and  foreach(var item in result)
and  and Console.WriteLine('{0,3} {1,-15} {2,-10}',item.ID, item.StudentName,item.SubjectName);
and  //
and  Console.ReadLine();
and }
}
class Student
{
public int StudentId { get; set; }
public string Name { get; set; }
public int Marks { get; set; }
public int SubjectId { get; set; }
}
class Subject{
and public int SubjectId { get; set; }
and public string SubjectName { get; set; }
}	
Output 
Inner Join
and ID StudentName     Subject   
and  1 Ashu            Cand#43;and#43;       
and  2 Shyam           C         
and  3 Shriyam         Cand#43;and#43;       
and  4 Sunny           Python    
and  5 Ram             C         
and  6 Krishna         Cand#43;and#43;       
and  7 Anupam          Java 
Left Outer Example -and 
using System;
using System.Collections.Generic;
using System.Linq;
public class Program
{
and public static void Main()
and {
and  Listandlt;Subject> subjects = new Listandlt;Subject>(){
and  and new Subject() { SubjectId = 1, SubjectName = 'C#'},
and  and new Subject() { SubjectId = 2, SubjectName = 'Cand#43;and#43;'},
and  and new Subject() { SubjectId = 3, SubjectName = 'C'},
and  and new Subject() { SubjectId = 4, SubjectName = 'Python'},
and  and new Subject() { SubjectId = 5, SubjectName = 'Java'}
and  };
and  Listandlt;Student> students = new Listandlt;Student>(){
and  new Student() { StudentId = 1, Name = 'Ashu', Marks = 500,SubjectId=2 },
and  new Student() { StudentId = 2, Name = 'Shyam', Marks = 500,SubjectId=3},
and  new Student() { StudentId = 3, Name = 'Shriyam', Marks = 400 },
and  new Student() { StudentId = 4, Name = 'Sunny', Marks = 550,SubjectId=4 },
and  new Student() { StudentId = 5, Name = 'Ram', Marks = 600,SubjectId=3 },
and  new Student() { StudentId = 6, Name = 'Krishna', Marks = 550,SubjectId=5 },
and  new Student() { StudentId = 7, Name = 'Anupam', Marks = 550 }
and  } ;
and  // left outer join command 
and  var result = from student in students
and  join subject in subjects
and  on student.SubjectId equals subject.SubjectId into stusub
and  from ss in stusub.DefaultIfEmpty()
and  select new
and  {
and  StudentName = student.Name,
and  SubjectName =  ss == null ? 'No Subject Choose' : ss.SubjectName,
and  ID = student.StudentId
and  };
and  Console.WriteLine('Left outer Join
');
and  Console.WriteLine('{0,3} {1,-15}  {2,-10}','ID', 'StudentName', 'Subject');
and  foreach(var item in result)
and  and Console.WriteLine('{0,3} {1,-15} {2,-10}',item.ID, item.StudentName,item.SubjectName);
and  //
and  Console.ReadLine();
and }
}
class Student
{
public int StudentId { get; set; }
public string Name { get; set; }
public int Marks { get; set; }
public int SubjectId { get; set; }
}
class Subject{
and public int SubjectId { get; set; }
and public string SubjectName { get; set; }
}
Output
Left outer Join
and ID StudentName      Subject   
and  1 Ashu            Cand#43;and#43;       
and  2 Shyam           C         
and  3 Shriyam         No Subject Choose
and  4 Sunny           Python    
and  5 Ram             C         
and  6 Krishna         Java      
and  7 Anupam          No Subject Choose
Cross Join LINQ
using System;
using System.Collections.Generic;
using System.Linq;
public class Program
{
and public static void Main()
and {
and  Listandlt;Subject> subjects = new Listandlt;Subject>(){
and  and new Subject() { SubjectId = 1, SubjectName = 'C#'},
and  and new Subject() { SubjectId = 2, SubjectName = 'Cand#43;and#43;'},
and  and new Subject() { SubjectId = 3, SubjectName = 'C'},
and  and new Subject() { SubjectId = 4, SubjectName = 'Python'},
and  and new Subject() { SubjectId = 5, SubjectName = 'Java'}
and  };
and  Listandlt;Student> students = new Listandlt;Student>(){
and  new Student() { StudentId = 1, Name = 'Ashu', Marks = 500,SubjectId=2 },
and  new Student() { StudentId = 2, Name = 'Shyam', Marks = 500,SubjectId=3},
and  new Student() { StudentId = 3, Name = 'Shriyam', Marks = 400 },
and  new Student() { StudentId = 4, Name = 'Sunny', Marks = 550,SubjectId=4 },
and  new Student() { StudentId = 5, Name = 'Ram', Marks = 600,SubjectId=3 },
and  new Student() { StudentId = 6, Name = 'Krishna', Marks = 550,SubjectId=5 },
and  new Student() { StudentId = 7, Name = 'Anupam', Marks = 550 }
and  } ;
and  // left outer join command 
and  var result = from student in students
and  and  and  from subject in subjects
and  select new
and  {
and  StudentName = student.Name,
and  SubjectName =  subject.SubjectName, 
and  ID = student.StudentId
and  };
and  Console.WriteLine('Cross Join
');
and  Console.WriteLine('{0,3} {1,-15}  {2,-10}','ID', 'StudentName', 'Subject');
and  foreach(var item in result)
and  and Console.WriteLine('{0,3} {1,-15} {2,-10}',item.ID, item.StudentName,item.SubjectName);
and  //
and  Console.ReadLine();
and }
}
class Student
{
public int StudentId { get; set; }
public string Name { get; set; }
public int Marks { get; set; }
public int SubjectId { get; set; }
}
class Subject{
and public int SubjectId { get; set; }
and public string SubjectName { get; set; }
}
Example
Cross Join
and ID StudentName      Subject   
and  1 Ashu            C#        
and  1 Ashu            Cand#43;and#43;       
and  1 Ashu            C         
and  1 Ashu            Python    
and  1 Ashu            Java      
and  2 Shyam           C#        
and  2 Shyam           Cand#43;and#43;       
and  2 Shyam           C         
and  2 Shyam           Python    
and  2 Shyam           Java      
and  3 Shriyam         C#        
and  3 Shriyam         Cand#43;and#43;       
and  3 Shriyam         C         
and  3 Shriyam         Python    
and  3 Shriyam         Java      
and  4 Sunny           C#        
and  4 Sunny           Cand#43;and#43;       
and  4 Sunny           C         
and  4 Sunny           Python    
and  4 Sunny           Java      
and  5 Ram             C#        
and  5 Ram             Cand#43;and#43;       
and  5 Ram             C         
and  5 Ram             Python    
and  5 Ram             Java      
and  6 Krishna         C#        
and  6 Krishna         Cand#43;and#43;       
and  6 Krishna         C         
and  6 Krishna         Python    
and  6 Krishna         Java      
and  7 Anupam          C#        
and  7 Anupam          Cand#43;and#43;       
and  7 Anupam          C         
and  7 Anupam          Python    
and  7 Anupam          Javaand  and and 
Cross Join LINQ
using System;using System.Collections.Generic;using System.Linq;
public class Program{and public static void Main()and {and  Listandlt;Subject> subjects = new Listandlt;Subject>(){and  and new Subject() { SubjectId = 1, SubjectName = 'C#'},and  and new Subject() { SubjectId = 2, SubjectName = 'Cand#43;and#43;'},and  and new Subject() { SubjectId = 3, SubjectName = 'C'},and  and new Subject() { SubjectId = 4, SubjectName = 'Python'},and  and new Subject() { SubjectId = 5, SubjectName = 'Java'}and  };
and  Listandlt;Student> students = new Listandlt;Student>(){and  new Student() { StudentId = 1, Name = 'Ashu', Marks = 500,SubjectId=2 },and  new Student() { StudentId = 2, Name = 'Shyam', Marks = 500,SubjectId=3},and  new Student() { StudentId = 3, Name = 'Shriyam', Marks = 400 },and  new Student() { StudentId = 4, Name = 'Sunny', Marks = 550,SubjectId=4 },and  new Student() { StudentId = 5, Name = 'Ram', Marks = 600,SubjectId=3 },and  new Student() { StudentId = 6, Name = 'Krishna', Marks = 550,SubjectId=5 },and  new Student() { StudentId = 7, Name = 'Anupam', Marks = 550 }and  } ;
and  // left outer join command 
and  var result = from subject in subjectsand  and  and  and and and and and and and and join student in studentsand  and and and and and and and and and and and and and on  subject.SubjectId equals student.SubjectId into stusuband  select newand  {and  SubjectName =  subject.SubjectName, and  Student = from stu in stusuband  and orderby stu.Nameand  and select stuand  };
and  Console.WriteLine('Group Join
');and  foreach(var item in result)and  {and  and Console.WriteLine(item.SubjectName and#43; ' Subject 
');and  and if(item.Student.ToList().Count() == 0)and  and  Console.WriteLine('	No availible student
');and  and elseand  and {and   item.Student.ToList().ForEach( stu => Console.WriteLine('	' and#43; stu.StudentId and#43; ' | ' and#43; stu.Name ));and  and  Console.WriteLine();and  and }and  }and  Console.ReadLine();and }}
class Student{public int StudentId { get; set; }public string Name { get; set; }public int Marks { get; set; }public int SubjectId { get; set; }
}
class Subject{and public int SubjectId { get; set; }and public string SubjectName { get; set; }}
Output
Group Join
C# Subject 
and  and  No availible student
Cand#43;and#43; Subject 
and  and  1 | Ashu
C Subject 
and  and  5 | Ramand  and  2 | Shyam
Python Subject 
and  and  4 | Sunny
Java Subject 
and  and  6 | Krishna

forum

How to use SQL Join in LINQ?

Ethan Karla

Can you answer this question?

1 Answers

Liked By